﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Configuration;
using System.Data.SqlClient;
using SHelper;
using DevExpress.XtraSplashScreen;
using DevExpress.XtraWaitForm;
using System.IO;

namespace InvoiceImport
{
    public partial class ReportForm3 : DevExpress.XtraEditors.XtraForm
    {
        public ReportForm3()
        {
            InitializeComponent();
        }

        string con = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public string ServerText { get; set; }
        public MainFrm mf { get; set; }
        DataTable SRtable, CBtable, Table1;
        string sdate1 = "", edate1 = "", sdate2 = "", edate2 = "", ItemName1 = "", ItemName2 = "";
        //行号
        private void gv_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator)
            {
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
            }
        }
        //查询
        private void SelectData()
        {
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在查询...");
            #region 查询条件赋值
            //if (string.IsNullOrEmpty(ItemText.Text))
            //{
            //    ItemName1 = ItemName2 = "";
            //}
            //else
            //{
            //    ItemName1 = string.Format("and c.cItemName like '%{0}%'", ItemText.Text);
            //    ItemName2 = string.Format("and a.cItemName like '%{0}%'", ItemText.Text);
            //}
            if (string.IsNullOrEmpty(Sdate.Text))
            {
                sdate1 = sdate2 = "";
            }
            else
            {
                sdate1 = string.Format("and d.dPBVDate>='{0}'", Sdate.Text);
                sdate2 = string.Format("and b.dDate>='{0}'", Sdate.Text);
            }
            if (string.IsNullOrEmpty(Edate.Text))
            {
                edate1 = edate2 = "";
            }
            else
            {
                edate1 = string.Format("and d.dPBVDate<='{0}'", Edate.Text);
                edate2 = string.Format("and b.dDate<='{0}'", Edate.Text);
            }
            #endregion
            #region 查询语句
            Table1 = SqlHelper.ExecuteDataTable(string.Format(@"
                        SELECT t.SBVID,t.DEPT,t.Business,t.MODE,t.[Debit To],t.Classification,t.[DN No.],t.Currency,t.[Sales(Original Amount)],t.[Trade Assistant],t.[Check(DEP)],
t.[Check(ADM)],t.[Booking date],t.Rate,t.[Sales (after CNY)],t.[VAT Rate],t.[Sales (excluded VAT)],t.[Temporary　Sales (Last Month)],t.[Temporary　Sales(This Month)],
t.[Sales TOTAL],t.[Actual Cost CNY (0%)],t.[Actual Cost CNY (6%)],t.[Actual Cost CNY (10%)],t.[Actual Cost CNY (17%)]
,(t.[Actual Cost CNY (0%)]+t.[Actual Cost CNY (6%)]+t.[Actual Cost CNY (10%)]+t.[Actual Cost CNY (17%)]) AS [Actual Cost CNY (included VAT)], 
(t.[Actual Cost CNY (0%)]+(t.[Actual Cost CNY (6%)]/1.06)+(t.[Actual Cost CNY (10%)]/1.10)+(t.[Actual Cost CNY (17%)]/1.17)) AS [Actual Cost CNY (excluded VAT)],
CASE WHEN t.[Actual Cost (Foreign Currency）]>0 THEN '美元' ELSE '人民币' END AS Currency1,t.[Actual Cost (Foreign Currency）],Rate1,
(t.[Actual Cost (Foreign Currency）] * Rate) AS [Actual Cost (after CNY)],((t.[Actual Cost (Foreign Currency）] * Rate)+(t.[Actual Cost CNY (0%)]+(t.[Actual Cost CNY (6%)]/1.06)+(t.[Actual Cost CNY (10%)]/1.10)+(t.[Actual Cost CNY (17%)]/1.17))) AS [Actual Cost (Excluded VAT)],
'' AS [Temporary Cost (Last Month)], '' AS [Temporary Cost (This Month)],((t.[Actual Cost (Foreign Currency）] * Rate)+(t.[Actual Cost CNY (0%)]+(t.[Actual Cost CNY (6%)]/1.06)+(t.[Actual Cost CNY (10%)]/1.10)+(t.[Actual Cost CNY (17%)]/1.17))) AS [Actual Cost TOTAL],
CONVERT(DECIMAL(20,2),(t.[Sales TOTAL]-((t.[Actual Cost (Foreign Currency）] * Rate)+(t.[Actual Cost CNY (0%)]+(t.[Actual Cost CNY (6%)]/1.06)+(t.[Actual Cost CNY (10%)]/1.10)+(t.[Actual Cost CNY (17%)]/1.17))))) AS MP
,
CONVERT(NVARCHAR(20),CONVERT(DECIMAL(20,2),((t.[Sales TOTAL]-((t.[Actual Cost (Foreign Currency）] * Rate)+(t.[Actual Cost CNY (0%)]+(t.[Actual Cost CNY (6%)]/1.06)+(t.[Actual Cost CNY (10%)]/1.10)+(t.[Actual Cost CNY (17%)]/1.17))))/ CASE WHEN t.[Sales TOTAL] = 0 THEN 1 ELSE t.[Sales TOTAL] END)*100))+'%' AS [MP%],'' AS REMARK

FROM (
SELECT t1.SBVID,CASE SUBSTRING(t2.cItemName,10,2) 
	                        WHEN 'CS' THEN '浦西'
	                        WHEN 'BE' THEN '北京'
	                        WHEN 'CK' THEN '重庆'
	                        WHEN 'XM' THEN '厦门'
	                        WHEN 'WG' THEN '外高桥'
	                        WHEN 'RE' THEN '瑞萨'
	                        ELSE '苏州'
                         END AS DEPT,
                        '' AS Business,'' AS MODE,'' AS [Debit To] ,'' AS Classification,t2.cItemName  AS [DN No.],t1.cexch_name AS Currency,
						CONVERT(DECIMAL(20,2),t2.iSum) AS [Sales(Original Amount)],t3.cPersonName AS [Trade Assistant],'' AS [Check(DEP)],'' AS [Check(ADM)],
						t1.dDate AS [Booking date],t1.iExchRate AS Rate,CONVERT(DECIMAL(20,2),(t2.iSum * t1.iExchRate)) AS [Sales (after CNY)],
						t2.iTaxRate AS [VAT Rate],
						CASE t2.iTaxRate 
						WHEN 0 THEN CONVERT(DECIMAL(20,2),(t2.iSum * t1.iExchRate))
						ELSE CONVERT(DECIMAL(20,2),(t2.iSum * t1.iExchRate)/(1+(t2.iTaxRate/100)))
						END AS [Sales (excluded VAT)],'' AS [Temporary　Sales (Last Month)],'' AS [Temporary　Sales(This Month)],
						CASE t2.iTaxRate 
						WHEN 0 THEN ISNULL(CONVERT(DECIMAL(20,2),(t2.iSum * t1.iExchRate)),0)
						ELSE ISNULL(CONVERT(DECIMAL(20,2),(t2.iSum * t1.iExchRate)/(1+(t2.iTaxRate/100))),0)
						END AS [Sales TOTAL],
						[Actual Cost CNY (0%)]=ISNULL(CONVERT(DECIMAL(20,2),(SELECT  SUM(CASE WHEN t6.iTaxRate >= 0 AND t6.iTaxRate < 6 THEN t6.iOriMoney ELSE 0 END) AS [Actual Cost CNY (0%)]
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0),
						[Actual Cost CNY (6%)]=ISNULL(CONVERT(DECIMAL(20,2),(SELECT  SUM(CASE WHEN t6.iTaxRate >= 6 AND t6.iTaxRate < 10 THEN t6.iSum ELSE 0 END) AS [Actual Cost CNY (0%)]
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0),
						[Actual Cost CNY (10%)]=ISNULL(CONVERT(DECIMAL(20,2),(SELECT  SUM(CASE WHEN  t6.iTaxRate >= 10 AND t6.iTaxRate < 17  THEN t6.iSum ELSE 0 END) AS [Actual Cost CNY (0%)]
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0),
						[Actual Cost CNY (17%)]=ISNULL(CONVERT(DECIMAL(20,2),(SELECT  SUM(CASE WHEN t6.iTaxRate >= 17 THEN t6.iSum ELSE 0 END) AS [Actual Cost CNY (0%)]
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0),
						[Actual Cost (Foreign Currency）]=ISNULL(CONVERT(DECIMAL(20,2),(SELECT  SUM(CASE t5.cexch_name WHEN '美元' THEN iOriMoney ELSE '' END) AS [Actual Cost CNY (0%)]
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0),
                        Rate1=ISNULL(CONVERT(DECIMAL(20,5),(SELECT  MAX(CASE t5.cexch_name WHEN '美元' THEN cExchRate ELSE '' END) 
							FROM PurBillVouch t5 LEFT JOIN PurBillVouchs t6 ON t5.PBVID = t6.PBVID WHERE t6.cItemCode= t2.cItemCode )),0)
                        FROM SaleBillVouch t1 LEFT JOIN dbo.SaleBillVouchs t2 ON t2.SBVID = t1.SBVID
                        LEFT JOIN Person t3 ON t1.cPersonCode=t3.cPersonCode ) t WHERE t.[Booking date] BETWEEN '" + Sdate.Text + " 00:00:00' and '" + Edate.Text + " 23:59:59' ORDER BY t.DEPT DESC"), con, new SqlParameter());
            #endregion
            #region 添加项目合计
            //string tempItemNmae="";
            //int r = Table1.Rows.Count;
            //for (int i = 0; i < r; i++)
            //{
            //    if (tempItemNmae != Table1.Rows[i]["项目名称"].ToString().Trim())
            //    {
            //        tempItemNmae = Table1.Rows[i]["项目名称"].ToString().Trim();
            //        DataRow dr = Table1.NewRow();
            //        dr["部门"] = Table1.Rows[i]["部门"].ToString().Trim();
            //        dr["项目名称"] = Table1.Rows[i]["项目名称"].ToString().Trim();
            //        dr["存货编码"] = "合计";
            //        dr["成本原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币无税金额");
            //        dr["成本原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币价税合计");
            //        dr["成本本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币无税金额");
            //        dr["成本本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币价税合计");
            //        dr["收入原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币无税金额");
            //        dr["收入原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币价税合计");
            //        dr["收入本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币无税金额");
            //        dr["收入本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币价税合计");
            //        Table1.Rows.Add(dr);
            //    }
            //}
            #endregion
            #region 添加利润合计
            //Table1.Columns.Add("原币无税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("原币含税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("本币无税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("本币含税利润", Type.GetType("System.Decimal"));
            //for (int i = 0; i < Table1.Rows.Count; i++)
            //{
            //    Table1.Rows[i]["原币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本原币无税金额"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["原币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本原币价税合计"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["本币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本本币无税金额"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["本币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本本币价税合计"].ToString())), 2).ToString("0.00");
            //}
            #endregion
            #region 添加总合计
            //DataView dv = Table1.DefaultView;
            //dv.Sort = "项目名称 Asc";
            //DataTable Table2 = dv.ToTable();
            //if (r > 0)
            //{
            //    DataRow dr = Table2.NewRow();
            //    dr["存货编码"] = "总合计";
            //    dr["成本原币无税金额"] = GetSum(Table1.Select(""), "成本原币无税金额");
            //    dr["成本原币价税合计"] = GetSum(Table1.Select(""), "成本原币价税合计");
            //    dr["成本本币无税金额"] = GetSum(Table1.Select(""), "成本本币无税金额");
            //    dr["成本本币价税合计"] = GetSum(Table1.Select(""), "成本本币价税合计");
            //    dr["收入原币无税金额"] = GetSum(Table1.Select(""), "收入原币无税金额");
            //    dr["收入原币价税合计"] = GetSum(Table1.Select(""), "收入原币价税合计");
            //    dr["收入本币无税金额"] = GetSum(Table1.Select(""), "收入本币无税金额");
            //    dr["收入本币价税合计"] = GetSum(Table1.Select(""), "收入本币价税合计");
            //    dr["原币无税利润"] = GetSum(Table1.Select(""), "收入原币无税金额");
            //    dr["原币含税利润"] = GetSum(Table1.Select(""), "收入原币价税合计");
            //    dr["本币无税利润"] = GetSum(Table1.Select(""), "收入本币无税金额");
            //    dr["本币含税利润"] = GetSum(Table1.Select(""), "收入本币价税合计");
            //    Table2.Rows.Add(dr);
            //}
            #endregion
            gc.DataSource = Table1;
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
        }
        //获取合计
        public string GetSum(DataRow[] drs, string ColumnName)
        {
            double sum = 0;
            foreach (var temp in drs)
            {
                try
                {
                    sum += double.Parse(temp[ColumnName].ToString());
                }
                catch { sum += 0; }
            }
            return Math.Round(sum, 2).ToString("0.00");
        }
        //加载
        private void ReportForm_Load(object sender, EventArgs e)
        {
            DateTime tempDate = DateTime.Today.AddMonths(-1);
            Sdate.DateTime = DateTime.Parse(tempDate.Year.ToString("0000") + "-" + tempDate.Month.ToString("00") + "-01");
            Edate.DateTime = DateTime.Parse(tempDate.Year.ToString("0000") + "-" + tempDate.Month.ToString("00") + "-" + DateTime.DaysInMonth(tempDate.Year, tempDate.Month).ToString("00"));
            con = con.Replace("UFDATA_999_2013", ServerText);
        }
        //查询按钮
        private void SelectBtn_Click(object sender, EventArgs e)
        {
            SelectData();
        }
        //回车查询
        private void ItemText_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                SelectData();
            }
        }
        //导出Excel
        private void ExPortBtn_Click(object sender, EventArgs e)
        {
            if (gv.RowCount < 1)
            {
                MessageBox.Show("没有需要导出的数据!", "提示");
                return;
            }
            SaveFileDialog dialog = new SaveFileDialog()
            {
                FileName = string.Format("收入成本报表{0}-{1}-{2}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day),
                Filter = "Execl 文件(*.xls)|*.xls|Execl 文件(*.xlsx)|*.xlsx"
            };
            FileInfo KGFileInfo = null;
            FileInfo KGFileInfo1 = null;
            if (dialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                KGFileInfo = new FileInfo(dialog.FileName);
                KGFileInfo1 = new FileInfo(dialog.FileName);
            }
            try
            {
                switch (KGFileInfo.Extension.ToLower())
                {
                    case ".xls":
                        gv.ExportToXls(dialog.FileName);
                        break;
                    case ".xlsx":
                        gv.ExportToXlsx(dialog.FileName);
                        break;
                    default:
                        MessageBox.Show(string.Format("尚未支持的格式{0}", KGFileInfo.Extension), "提示");
                        return;
                }
                XtraMessageBox.Show("导出成功!", "提示");
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show("导出失败!原因:" + ex, "提示");
            }
        }

        private void AreaSetting_Click(object sender, EventArgs e)
        {
            AreaSettingForm asf = new AreaSettingForm();
            asf.ShowDialog();
        }

        private void ReportForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            mf.Show();
        }
    }
}